package bookstore.tools;

import hibernate.HibernateSessionFactory;

import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

import org.hibernate.Session;

import bookstore.database.pojo.Book;
import bookstore.database.pojo.BookType;
import bookstore.database.pojo.BookTypeDAO;
import bookstore.database.pojo.Image;
import bookstore.database.pojo.ImageDAO;

public class Tools {
	public static boolean isCustomerSeller(String mail) {
		boolean flag = false;
		int is = ((BigDecimal)HibernateSessionFactory.getSession().
		createSQLQuery("select is_cus_seller('" + mail + "') as flag from dual").
		uniqueResult()).intValue() ;
		if(is == 1) { 
			flag = true ;
		}
		return flag;
	}
	public static List<Book> getBooks(String type) {
		Connection conn = HibernateSessionFactory.getSession().connection();
		String callSql = "{? = call bookwithtype.getbooks(?,?)}";
		CallableStatement cstmt;
		List<Book> books = new LinkedList<Book>() ;
		Book book ;
			try {
				cstmt = conn.prepareCall(callSql);
				cstmt.setString(2, type);
				cstmt.setInt(3, 1);
				cstmt.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);
				cstmt.execute();
				ResultSet rs = (ResultSet) cstmt.getObject(1);
				if (rs != null) {

					while (rs.next()) {
						book = new Book() ;
						book.setIsbn(rs.getString(1));
						BookType booType = new BookTypeDAO().
						findById(rs.getBigDecimal(2));
						book.setBookType(booType);
						book.setBname(rs.getString(3));
						book.setBprice(rs.getBigDecimal(4)) ;
						book.setPress(rs.getString(5));
						Image image = new ImageDAO().findById(rs.getBigDecimal(6) ) ;
						book.setImage(image);
						book.setAuthor(rs.getString(7)) ;
						books.add(book);
					}
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		return books;
	}
	
	public static int getPageSize(String type) {
		Session session = HibernateSessionFactory.getSession();
		int count = ((BigDecimal)session.createSQLQuery(
				"select getrecordnum('"+type + "') from dual").
				uniqueResult()).intValue();
		
		return count % 8 == 0? count / 8 : count / 8 + 1;
	}
	
	public static int getSearchPageSize(String type,String keyWords) {
		Session session = HibernateSessionFactory.getSession();
		int count = ((BigDecimal)session.createSQLQuery(
				"select get_search_num('" +
				 type + "','" + keyWords + "') from dual").
				uniqueResult()).intValue();
		
		return count % 8 == 0? count / 8 : count / 8 + 1;
	}
}
